﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using Common;

namespace DAL
{
    public class SQLHepler
    {
        private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
        #region 普通的SQL语句()
        /// <summary>
        /// 普通的SQL语句()
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql)
        {
            //数据库连接对象

            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            SqlCommand cmd = new SqlCommand(sql, conn);

            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            //cmd.CommandText = sql;
            //cmd.CommandType = CommandType.StoredProcedure;
            //cmd.Connection=conn;

        }
        #endregion
        #region 执行单一结果查询

        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回单一结果</returns>
        public static object GetScalar(string sql)
        {
            //
            //数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            SqlCommand cmd = new SqlCommand(sql, conn);

            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        #region 获取datareader读取器
        /// <summary>
        /// 获取datareader读取器
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回读取器datareader对象</returns>
        public static SqlDataReader GetDataReader(string sql)
        {
            //
            //数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            SqlCommand cmd = new SqlCommand(sql, conn);

            try
            {
                conn.Open();
                //关闭datareader时关闭数据库连接
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            //不能直接关闭
            //数据没有读取完不能关闭，关闭后无法读取

            //finally
            //{
            //    conn.Close();
            //}
        }
        #endregion
        #region 获取dataset数据集
        /// <summary>
        /// 获取dataset数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回数据集dataset对象</returns>
        public static DataSet GetDataset(string sql)
        {
            //数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            SqlCommand cmd = new SqlCommand(sql, conn);
            //数据集对象
            DataSet ds = new DataSet();
            //创建适配器对象
            SqlDataAdapter sqlData = new SqlDataAdapter(cmd);
            try
            {
                conn.Open();
                //创建数据集对象（相当于前端虚拟的数据库，保存再内存中）

                sqlData.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        #region 获取数据库服务器时间-同一时间
        /// <summary>
        /// 获取数据库服务器时间-同一时间
        /// </summary>
        /// <returns>返回数据库时间</returns>
        public static DateTime GetDateTime()
        {
            return Convert.ToDateTime(GetScalar("select getdate();"));
        }
        #endregion
        #region 获取datatable数据表(内存中)
        /// <summary>
        /// 获取datatable数据表(内存中)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回datatable对象</returns>
        public static DataTable GetDateTime(string sql)
        {
            //数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            try
            {
                conn.Open();
                //关闭datareader时关闭数据库连接
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        #region 获取dataview视图(内存中)
        /// <summary>
        /// 获取dataview视图(内存中)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回datatable对象</returns>
        public static DataView GetDatView(string sql)
        {
            //数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            DataTable dt = new DataTable();
            //创建数据视图对象
            DataView dataView = new DataView();
            //创建适配器对象
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            try
            {
                conn.Open();
                //关闭datareader时关闭数据库连接
                adapter.Fill(dt);
                dataView.Table = dt;
                return dataView;
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            //不能直接关闭
            //数据没有读取完不能关闭，关闭后无法读取

            finally
            {
                conn.Close();
            }

        }
        #endregion
        #region 基于事务提交数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool UpdateByTransaction(string str)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {

                conn.Open();
                cmd.Transaction = conn.BeginTransaction();

                cmd.CommandText = str;
                cmd.ExecuteNonQuery();

                cmd.Transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                //如果事务不为空，开始回滚，将会一条数据也不能插入成功(就算是在中间的异常)
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();
                }
                throw new Exception("基于事务提交数据异常: " + ex.Message);
            }
            finally
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction = null;
                }
                conn.Close();
            }
        }
        #endregion


        #region 普通的SQL语句() 有参sql语句
        /// <summary>
        /// 普通的SQL语句()
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql, SqlParameter[] parameters)
        {
            //数据库连接对象

            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(parameters);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            //cmd.CommandText = sql;
            //cmd.CommandType = CommandType.StoredProcedure;
            //cmd.Connection=conn;

        }
        #endregion
        #region 执行单一结果查询 有参sql语句

        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回单一结果</returns>
        public static object GetScalar(string sql, SqlParameter[] parameters)
        {
            //
            //数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(parameters);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        #region 获取datareader读取器
        /// <summary>
        /// 获取datareader读取器
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回读取器datareader对象</returns>
        public static SqlDataReader GetDataReader(string sql, SqlParameter[] parameters)
        {
            //
            //数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(parameters);
            try
            {
                conn.Open();
                //关闭datareader时关闭数据库连接
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            //不能直接关闭
            //数据没有读取完不能关闭，关闭后无法读取

            //finally
            //{
            //    conn.Close();
            //}
        }
        #endregion
        #region 获取dataset数据集
        /// <summary>
        /// 获取dataset数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回数据集dataset对象</returns>
        public static DataSet GetDataset(string sql, SqlParameter[] parameters)
        {
            //数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //创建执行的SQL语句命令对象
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(parameters);
            //数据集对象
            DataSet ds = new DataSet();
            //创建适配器对象
            SqlDataAdapter sqlData = new SqlDataAdapter(cmd);
            try
            {
                conn.Open();
                //创建数据集对象（相当于前端虚拟的数据库，保存再内存中）

                sqlData.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                //异常写入日志
                FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

    }
}
